package locacao.integracao.dao.pstPostgreSQL;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import locacao.integracao.dao.VeiculoDAO;
import locacao.negocio.to.VeiculoTO;

public class VeiculoPgsqlDAO implements VeiculoDAO {

    private ArrayList<VeiculoTO> veiculos;

    public VeiculoPgsqlDAO(){
	   setVeiculos(new ArrayList<VeiculoTO>());
    }


    public void addOrUpdateVeiculo(VeiculoTO veiculo) {
    	Connection conn = null;
    	CallableStatement spManterVeiculo = null;

    	try {        	
          String sql = "{call locacao.manter_veiculo(?,?,?,?,?,?,?,?,?)}";
          conn = ConnectionRentACar.getInstance().getConnection();
          
          spManterVeiculo = conn.prepareCall(sql);
          
          spManterVeiculo.setInt(1, veiculo.getIdVeiculo());
          spManterVeiculo.setString(2, veiculo.getFabricante());
          spManterVeiculo.setString(3, veiculo.getModelo());
          spManterVeiculo.setInt(4, veiculo.getAno());
          spManterVeiculo.setInt(5, veiculo.getQtdPortas());
          spManterVeiculo.setString(6, veiculo.getCor());
          spManterVeiculo.setString(7, veiculo.getPlaca());
          spManterVeiculo.setString(8, veiculo.getCombustivel());
          spManterVeiculo.setString(9, veiculo.getStatus());
          
          spManterVeiculo.execute();
        } catch (Exception e) {
          System.out.println(e.getMessage());
        } finally {
          try {
            if (spManterVeiculo != null)
            	spManterVeiculo.close();

          } catch (SQLException e) {
            System.out.println(e.getMessage());
          }
        }
    }

    /**
     *

    /**
     * @param veiculo
     */
    public void excludeVeiculo( VeiculoTO veiculo )  {
    	Connection conn = null;
    	CallableStatement spDeleteVeiculo = null;

    	try {        	
          String sql = "{call locacao.delete_veiculo(?)}";
          conn = ConnectionRentACar.getInstance().getConnection();
          
          spDeleteVeiculo = conn.prepareCall(sql);
          
          spDeleteVeiculo.setInt(1, veiculo.getIdVeiculo());         
          
          spDeleteVeiculo.execute();
        } catch (Exception e) {
          System.out.println(e.getMessage());
        } finally {
          try {
            if (spDeleteVeiculo != null)
            	spDeleteVeiculo.close();

          } catch (SQLException e) {
            System.out.println(e.getMessage());
          }
        }
    }


  
    /**
     * @return
     */
    public String[] findVeiculoById(Integer id){
    	 Connection conn = null;
    	 Statement stmt = null;
    	 ResultSet rSet = null;
    	 String[] info = null;

    	 String sql = "SELECT  idveiculo, fabricante, modelo, ano, qtdportas, cor, placa, combustivel, status ";
    	 sql += " FROM locacao.veiculo WHERE idveiculo =  " + id;

    	 try {
    		 conn = ConnectionRentACar.getInstance().getConnection();
    		 stmt = conn.createStatement();
    		 rSet = stmt.executeQuery(sql);

    		 if (rSet != null) {    			 
    			 while (rSet.next()) {
    				 
    				info = new String[veiculos.size()];
    	    		   info[0] = rSet.getInt("idveiculo") 
    	       		   + " - " + rSet.getString("fabricante")
    	       		   + " - " + rSet.getString("modelo")
    	       		   + " - " + rSet.getInt("ano")
    	       		   + " - " + rSet.getInt("qtdPortas")
    	       		   + " - " + rSet.getString("cor")
    	       		   + " - " + rSet.getString("placa")                    
    	               + " - " + rSet.getString("combustivel")
    	               + " - " + rSet.getString("status");    	    		   
    			 }    			 
    		 }
			 return info;
			 
    	 } catch (Exception e) {
    		 System.out.println(e.getMessage());
    		 return null;
    	 } finally {
    		 try {
    			 if (rSet != null)
    				 rSet.close();
    			 if (stmt != null)
    				 stmt.close();
    		 } catch (SQLException e) {
    			 System.out.println(e.getMessage());
    		 }
    	 }
    }

    
    /**
     * @return
     */
    public String[] getInfoVeiculos()
    {
        VeiculoTO veiculo;
        veiculos = getVeiculos();
        String[] info = new String[veiculos.size()];
        Iterator iteratorVeiculos;
        iteratorVeiculos = veiculos.iterator();
        int i=0;
        while ( iteratorVeiculos.hasNext() )
        {
            veiculo = (VeiculoTO) iteratorVeiculos.next();
            info[i] = veiculo.getIdVeiculo() 
            		+ " - " + veiculo.getFabricante()   
            		+ " - " + veiculo.getModelo()
            		+ " - " + veiculo.getAno()
            		+ " - " + veiculo.getQtdPortas()
            		+ " - " + veiculo.getCor()
            		+ " - " + veiculo.getPlaca()
            		+ " - " + veiculo.getCombustivel()
            		+ " - " + veiculo.getStatus();
            i++;
        }
        return info;
    }


    /**
     * @return
     */
    public ArrayList<VeiculoTO> getVeiculos() {
    	 Connection conn = null;
    	 Statement stmt = null;
    	 ResultSet rSet = null;
    	 VeiculoTO veiculo = null;

    	 String sql = "SELECT  idveiculo, fabricante, modelo, ano, qtdportas, cor, placa, combustivel, status ";
    	 sql += " FROM locacao.veiculo ORDER BY idveiculo";

    	 try {
    		 conn = ConnectionRentACar.getInstance().getConnection();
    		 stmt = conn.createStatement();
    		 rSet = stmt.executeQuery(sql);

    		 if (rSet != null) {
    			 veiculos = new ArrayList();
    			 while (rSet.next()) {
    				 
    				 veiculo = new VeiculoTO();
    				 
    				 veiculo.setIdVeiculo(rSet.getInt("idveiculo"));
    				 veiculo.setFabricante(rSet.getString("fabricante"));
    				 veiculo.setModelo(rSet.getString("modelo"));
    				 veiculo.setAno(rSet.getInt("ano"));
    				 veiculo.setQtdPortas(rSet.getInt("qtdportas"));
    				 veiculo.setCor(rSet.getString("cor"));
    				 veiculo.setPlaca(rSet.getString("placa"));
    				 veiculo.setCombustivel(rSet.getString("combustivel"));
    				 veiculo.setStatus(rSet.getString("status"));
    				 
    				 veiculos.add(veiculo);
    			 }
    		 }
    	 } catch (Exception e) {
    		 System.out.println(e.getMessage());
    	 } finally {
    		 try {
    			 if (rSet != null)
    				 rSet.close();
    			 if (stmt != null)
    				 stmt.close();
    		 } catch (SQLException e) {
    			 System.out.println(e.getMessage());
    		 }
    	 }
    	 return veiculos;
    }

    private void setVeiculos(ArrayList<VeiculoTO> veiculos) {
    	this.veiculos = veiculos;
    }
    
}
